package com.htht.smartschool.service.attendance;

import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;

import com.htht.smartschool.common.service.BaseService;
import com.htht.smartschool.model.attendance.AttendanceIntoSchoolInfo;
import com.htht.smartschool.model.attendance.AttendanceStaticsQueryResult;
import com.htht.smartschool.model.attendance.AttendanceStaticsQueryResultTeacher;
import com.htht.smartschool.model.attendance.AttendanceStudentInfo;
import com.htht.smartschool.model.attendance.AttendanceWebUserQueryResult;
import com.htht.smartschool.model.attendance.IntoSchoolRecordQueryResult;
import com.htht.smartschool.model.attendance.TAttendance;
import com.htht.smartschool.model.attendance.TAttendanceRegular;
import com.htht.smartschool.model.attendance.TAttendanceStatistics;
import com.htht.smartschool.model.message.TMessage;
import com.htht.smartschool.model.teacher.TClass;
import com.htht.smartschool.service.teacher.TeacherService;
import com.htht.smartschool.util.system.DateUtil;
import com.htht.smartschool.util.system.GsonUtils;
import com.htht.smartschool.util.system.ReflectionUtils;
import com.htht.smartschool.util.system.ResultSetToExcel;

public class AttendanceService extends BaseService{

	public boolean addAttendance(TAttendance attendance) {
		boolean flag = false;
		TAttendance tAttendance =(TAttendance)getDao().create(attendance);
		if(tAttendance!=null){
			flag = true;
		}
		return flag;
	}

	public boolean updateAttendance(TAttendance attendance) {
		boolean flag = false;
		TAttendance tAttendance = (TAttendance) getDao().update(attendance);
		if(tAttendance!=null){
			flag = true;
		}
		return flag;
	}

	public boolean deleteAttendance(TAttendance attendance) {
		return getDao().delete(attendance);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<TAttendance> getAttendance(TAttendance attendance) {
		String hql = "select ta,ts.name from TAttendance ta,TStudent ts where ta.time <= :endtime and ta.time >= :starttime and ta.studentcode = ts.studentcode";
		if(attendance.getStudentcode() != null && attendance.getStudentcode().length() > 0){
			hql += " and ta.studentcode = :studentcode";
		}
		if(attendance.getClasscode() != null && attendance.getClasscode().length() > 0){
			hql += " and ta.classcode = :classcode";
		}
		if(attendance.getTimetype() != null &&  attendance.getTimetype() > 0){
			hql += " and ta.timetype = :timetype";
		}
		List<Object[]> list = getDao().queryByHQL(hql, attendance);
		List<TAttendance> result = new ArrayList<TAttendance>();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			TAttendance ta = (TAttendance) obj[0];
			ta.setStudentname(String.valueOf(obj[1]));
			result.add(ta);
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getAttendanceByPage(TAttendance attendance) {
		String hql = "select  ta,ts.name,tc from TAttendance ta,TStudent ts,TClass tc where ts.classcode = tc.classcode and ta.time <= :endtime and ta.time >= :starttime and ta.studentcode = ts.studentcode";
		Map<String,Object> param = new HashMap<String,Object>();
		param.put("starttime", attendance.getStarttime());
		param.put("endtime", attendance.getEndtime());
		if(attendance.getStudentcode() != null && attendance.getStudentcode().length() > 0){
			hql += " and ta.studentcode = :studentcode";
			param.put("studentcode",attendance.getStudentcode());
		}
		if(attendance.getClasscode() != null && attendance.getClasscode().length() > 0){
			if(attendance.getClasscode().contains(",")){
				attendance.setClasscode(attendance.getClasscode().replace(",", "^"));
			}
			hql += " and ta.classcode in (:classcode)";
			param.put("classcode", attendance.getClasscode());
		}
		if(attendance.getTimetype() != null &&  attendance.getTimetype() > 0){
			hql += " and ta.timetype = :timetype";
			param.put("timetype", attendance.getTimetype());
		}
		if(attendance.getType() != null &&  attendance.getType().length() > 0){
			hql += " and ta.type = :type";
			param.put("type", attendance.getType());
		}
		if(attendance.getClassinfo() != null && attendance.getClassinfo().getClass_() != null && attendance.getClassinfo().getClass_() > 0){
			hql += " and tc.class = :class_";
			param.put("class_", attendance.getClassinfo().getClass_());
		}
		if(attendance.getClassinfo() != null && attendance.getClassinfo().getGrade() != null && attendance.getClassinfo().getGrade() > 0){
			hql += " and tc.grade in (:grade)";
			param.put("grade", attendance.getClassinfo().getGrade());
		}
		if(attendance.getClassinfo() != null && attendance.getClassinfo().getType() != null && attendance.getClassinfo().getType().length() > 0){
			hql += " and tc.type = :type";
			param.put("type", attendance.getClassinfo().getType());
		}
		if(attendance.getClassinfo() != null && attendance.getClassinfo().getSchoolcode() != null && attendance.getClassinfo().getSchoolcode().length() > 0){
			hql += " and tc.schoolcode = :schoolcode";
			param.put("schoolcode", attendance.getClassinfo().getSchoolcode());
		}
		if(attendance.getClassinfo() != null && attendance.getClassinfo().getClasscode() != null && attendance.getClassinfo().getClasscode().length() > 0){
			String classcode = attendance.getClassinfo().getClasscode();
			hql += " and tc.classcode in (:classcode)";
			if(classcode.contains(",")){
				classcode.replace(",", "^");			
			}
			param.put("classcode", classcode);
		}
		
		hql += " GROUP BY ta.studentcode,FROM_UNIXTIME(ta.time/1000, '%Y-%c-%d %h:%i')  order by ta.time desc";
		List list =  null;
		if(attendance.getCurrentpage() != null && attendance.getCurrentpage() > 0 && attendance.getPagesize() != null && attendance.getPagesize() > 0){
			list = getDao().queryByHQLPage(hql,param, attendance.getCurrentpage(), attendance.getPagesize());
		}else{
			list = getDao().queryByHQL(hql,param);
		}
		List list2 = getDao().queryByHQL(hql,param);
		List<TAttendance> result = new ArrayList<TAttendance>();
		for (int i = 0; i < list.size(); i++) {
			Object[] obj = (Object[]) list.get(i);
			TAttendance ta = (TAttendance) obj[0];
			ta.setStudentname(String.valueOf(obj[1]));
			ta.setClassinfo((TClass)obj[2]);
			result.add(ta);
		}
		Map map = new HashMap();
		map.put("total", list2.size());
		map.put("data", result);
		return map;
	}
	@SuppressWarnings("unchecked")
	public List<TAttendanceStatistics> getAttendanceByClasscode(
			TAttendance attendance) {
		String hql = "from TAttendanceStatistics where time <= :endtime and time >= :starttime";
		if(attendance.getClasscode() != null){
			hql += " and classcode = :classcode";
		}
		List<TAttendanceStatistics> list = getDao().queryByHQL(hql,attendance);
		return list;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getAttendanceByClasscodeByPage(
			TAttendance attendance) {
		List<TAttendanceStatistics> listResult = new ArrayList<TAttendanceStatistics>();
		String sql = "select ta.classcode,ta.studentnum,ta.normalnum,ta.leavenum,ta.abnormalnum,FROM_UNIXTIME(ta.time,'%Y-%m-%d %H:%i:%S'),ta.timetype,ta.teachercode,tc.grade,tc.class,tt.name from t_attendance_statistics ta,t_class tc,t_teacher tt where time <= :endtime and time >= :starttime and ta.classcode = tc.classcode and tc.teachercode = tt.teachercode";
		if(attendance.getClasscode() != null){
			sql += " and ta.classcode = :classcode";
		}
		List<Object[]> list = null;
		if(attendance.getCurrentpage() == null || attendance.getCurrentpage() == 0){
			list = getDao().queryBySQL(sql,attendance);
		}else{
			list = getDao().queryBySqlPage(sql,attendance, attendance.getCurrentpage(), attendance.getPagesize());
		}
		List listCount = getDao().queryBySQL(sql,attendance);
		ResultSetToExcel rse = null;
		if(attendance.getIsdownload() != null && attendance.getIsdownload().equalsIgnoreCase("true")){
			rse = new ResultSetToExcel();
			rse.setColoumItem( "classcode,studentnum,normalnum,leavenum,abnormalnum,time,timetype,teachercode,grade,class,name");
			rse.setRs(list);
			if(list.size() > 0){
				rse.writeExcel();
			}
		}
		int count = listCount.size();
		for(Object[] obj : list){
			TAttendanceStatistics tAttendanceStatistics = new TAttendanceStatistics();
			tAttendanceStatistics.setClasscode(String.valueOf(obj[0]));
			tAttendanceStatistics.setStudentnum(Long.valueOf(String.valueOf(obj[1])));
			tAttendanceStatistics.setNormalnum(Long.valueOf(String.valueOf(obj[2])));
			tAttendanceStatistics.setLeavenum(Long.valueOf(String.valueOf(obj[3])));
			tAttendanceStatistics.setAbnormalnum(Long.valueOf(String.valueOf(obj[4])));
			tAttendanceStatistics.setTime(DateUtil.getTimeBySomeDateFormat(String.valueOf(obj[5]),"yyyy-MM-dd HH:mm:ss"));
			tAttendanceStatistics.setTimetype(Integer.valueOf(String.valueOf(obj[6])));
			tAttendanceStatistics.setTeachercode(String.valueOf(obj[7]));
			tAttendanceStatistics.setGrade(String.valueOf(obj[8]));
			tAttendanceStatistics.setTclass(String.valueOf(obj[9]));
			tAttendanceStatistics.setName(String.valueOf(obj[10]));
			tAttendanceStatistics.setTotal((long) count);
			listResult.add(tAttendanceStatistics);
		}
		Map map = new HashMap();
		map.put("list", listResult);
		if(rse != null){
			rse.setRs(null);
			map.put("remark", rse);
		}
		return map;
	}
	public boolean addAttendanceStatistics(
			TAttendanceStatistics tattendancestatistics) {
		boolean flag = false;
		TAttendanceStatistics tAttendance =(TAttendanceStatistics)getDao().create(tattendancestatistics);
		if(tAttendance!=null){
			flag = true;
		}
		return flag;
	}

	@SuppressWarnings("unchecked")
	public List<ClassInfo> getAllClass() {
		String sql = "SELECT tc.class,tc.classcode,tc.teachercode,tc.name,GROUP_CONCAT(ts.studentcode ORDER BY ts.studentcode ASC) AS studentcodes FROM ( "
				+" SELECT CONCAT(t.grade,'年级',t.class,'班') AS class,t.classcode,t.teachercode,tt.name FROM t_class t,t_teacher tt WHERE t.teachercode = tt.teachercode ORDER BY t.grade ASC"
				+" )tc LEFT JOIN t_student ts ON tc.classcode = ts.classcode GROUP BY tc.class ORDER BY tc.classcode ASC";
		List<Object[]> list = getDao().queryBySQL(sql);
		List<ClassInfo> result = new ArrayList<ClassInfo>();
		for (Object[] obj : list) {
			ClassInfo cl = new ClassInfo();
			cl.setClasz(String.valueOf(obj[0]));
			cl.setClasscode(String.valueOf(obj[1]));
			cl.setTeachercode(String.valueOf(obj[2]));
			cl.setTeachername(String.valueOf(obj[3]));
			String studentcodesString = String.valueOf(obj[4]);
			if(studentcodesString != null && !studentcodesString.equalsIgnoreCase("null") && studentcodesString.length() > 0){
				String[] studentcodes = studentcodesString.split(",");
				cl.setStudentcodes(Arrays.asList(studentcodes));
			}
			result.add(cl);
		}
		return result;
	}

	/**查询学生的信息
	 * @author:wuqiwei
	 * @param @param attendanceIntoSchoolInfo
	 * @param @return
	 * @date:2016年1月14日下午3:28:33
	*/
	@SuppressWarnings("unchecked")
	public List<AttendanceIntoSchoolInfo> getStudentInfo(
			AttendanceIntoSchoolInfo attendanceIntoSchoolInfo) {
		String sql ="select * from t_intoschool_record WHERE id >= '1'";
		if(attendanceIntoSchoolInfo.getImei() != null && attendanceIntoSchoolInfo.getImei().length()>0){
			sql +=" and imei = :imei";
		}
		if(attendanceIntoSchoolInfo.getStudentcode() != null && attendanceIntoSchoolInfo.getStudentcode().length()>0){
			sql +=" and studentcode = :studentcode";
		}
		if(attendanceIntoSchoolInfo.getClasscode() !=null && attendanceIntoSchoolInfo.getClasscode().length()>0) {
			sql +=" and classcode = :classcode";
		}
		if( attendanceIntoSchoolInfo.getGrade() != null && attendanceIntoSchoolInfo.getGrade().length()>0){
			sql +=" and grade = :grade";
		}
		if( attendanceIntoSchoolInfo.getTheClass() != null && attendanceIntoSchoolInfo.getTheClass().length()>0){
			sql +=" and class = :class";
		}
		if( attendanceIntoSchoolInfo.getName() != null && attendanceIntoSchoolInfo.getName().length()>0){
			sql +=" and name = :name";
		}
		if(attendanceIntoSchoolInfo.getStudytype() != null && attendanceIntoSchoolInfo.getStudytype().length()>0){
			sql +=" and studytype = :studytype";
		}
		if(attendanceIntoSchoolInfo.getUsercode() != null && attendanceIntoSchoolInfo.getUsercode().length()>0){
			sql +=" and usercode = :usercode";
		}
		if(attendanceIntoSchoolInfo.getStatus() != null && attendanceIntoSchoolInfo.getStatus().length()>0){
			sql +=" and status =:status";
		}
		if(attendanceIntoSchoolInfo.getTime() != null && attendanceIntoSchoolInfo.getTime().length()>0){
			sql +=" and time =:time";
		}
		List<Object[]> list = getDao().queryBySQL(sql,attendanceIntoSchoolInfo);
		List<AttendanceIntoSchoolInfo> result = new ArrayList<AttendanceIntoSchoolInfo>();
		for(Object[] obj : list){
			AttendanceIntoSchoolInfo info = new AttendanceIntoSchoolInfo();
			info.setImei(String.valueOf(obj[1]));
			info.setStudentcode(String.valueOf(obj[2]));
			info.setClasscode(String.valueOf(obj[3]));
			info.setGrade(String.valueOf(obj[4]));
			info.setTheClass(String.valueOf(obj[5]));
			info.setName(String.valueOf(obj[6]));
			info.setStudytype(String.valueOf(obj[7]));
			info.setUsercode(String.valueOf(obj[7]));
			info.setStatus(String.valueOf(obj[8]));
			info.setTime(String.valueOf(obj[9]));
			result.add(info);
		}
		return result;
	}

	/**更新考勤规则
	 * @author:wuqiwei
	 * @param @param attendanceregular
	 * @param @return
	 * @date:2016年2月25日上午10:40:27
	*/
	public boolean updateAttendanceRegular(TAttendanceRegular attendanceregular) {
		String hql = "from TAttendanceRegular where id = '"+attendanceregular.getId()+"'";
		TAttendanceRegular tc = (TAttendanceRegular) getDao().queryFirstByHQL(hql);
		for (Field f : attendanceregular.getClass().getDeclaredFields()) {
		    f.setAccessible(true);
		    try {
				if (f.get(attendanceregular) != null && !f.getName().equalsIgnoreCase("serialVersionUID")) {
				   f.set(tc, f.get(attendanceregular));
				}
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}
		}
		TAttendanceRegular tr = (TAttendanceRegular) getDao().update(tc);
		TeacherService teacherService = new TeacherService();
		TMessage tme = new TMessage();
		tme.setType("1");
		tme.setSendcode(attendanceregular.getSendcode());
		tme.setReceivecode("11");
		tme.setContent("考勤规则："+attendanceregular.getName()+"，已修改。");
		tme.setIdatetime(new Date().getTime());
		teacherService.sendMessage(tme);
		return tr == null ? false : true;
	}

	/**删除考勤规则
	 * @author:wuqiwei
	 * @param @param attendanceregular
	 * @param @return
	 * @date:2016年2月25日上午10:53:58
	*/
	public boolean deleteAttendanceRegular(TAttendanceRegular attendanceregular) {
		boolean flag = false;
		String sql = " delete from t_attendance_regular where id = '"+attendanceregular.getId()+"'";
		int a = getDao().executeSQL(sql);
		if (a >= 0) {
			flag = true;
		}
		return flag;
	}

	/**添加考勤规则
	 * @author:wuqiwei
	 * @param @param attendanceregular
	 * @param @return
	 * @date:2016年2月25日上午10:56:42
	*/
	@SuppressWarnings("unchecked")
	public boolean insertAttendanceRegular(TAttendanceRegular attendanceregular) {
		String hql = "From TAttendanceRegular where code = :code";
		List<TAttendanceRegular> list = getDao().queryByHQL(hql,attendanceregular);
		if(list.size()>0){
			return false;
		}else{
			TAttendanceRegular a = (TAttendanceRegular) getDao().create(attendanceregular);
			
			TeacherService teacherService = new TeacherService();
			TMessage tme = new TMessage();
			tme.setType("1");
			tme.setSendcode(attendanceregular.getSendcode());
			tme.setReceivecode("11");
			tme.setContent("考勤规则："+attendanceregular.getName()+"，已创建。");
			tme.setIdatetime(new Date().getTime());
			tme.setSchoolcode(attendanceregular.getSchoolcode());
			teacherService.sendMessage(tme);
			
			return a == null ? false :true;
		}
	}

	/**查询考勤规则
	 * @author:wuqiwei
	 * @param attendanceregular 
	 * @param @return
	 * @date:2016年2月25日上午11:05:57
	*/
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map queryAttendanceRegular(TAttendanceRegular attendanceregular) {
		Map map = new HashMap();
		String hql = "select ta,t.alias From TAttendanceRegular ta,TUser t where ta.userkey = t.userkey";
		if(attendanceregular.getSchoolcode()!= null && attendanceregular.getSchoolcode().length() >0){
			hql +=  " and ta.schoolcode = :schoolcode";
		}
		if(attendanceregular.getName()!= null && attendanceregular.getName().length() >0){
			hql +=  " and ta.name like :name";
			attendanceregular.setName("%"+attendanceregular.getName()+"%");
		}
		if(attendanceregular.getCode()!= null && attendanceregular.getCode().length() >0){
			hql +=  " and ta.code = :code";
		}
		List<Object[]> list = getDao().queryByHQLPage(hql,attendanceregular,attendanceregular.getCurrentpage(),attendanceregular.getPagesize());
		List<TAttendanceRegular> result = new ArrayList<TAttendanceRegular>();
		for (Object[] obj : list) {
			TAttendanceRegular tr = (TAttendanceRegular) obj[0];
			tr.setCreateuser(String.valueOf(obj[1]));
			result.add(tr);
		}
		int counts  = getDao().queryByHQL(hql,attendanceregular).size();
		map.put("data", result);
		map.put("total", counts);
		return map;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map queryAttendanceByAdmin(AttendanceIntoSchoolInfo as) {
		String[] grades = as.getGrade().split(",");
		String sql = "SELECT cl.grade, t.studentnum, sum(t.leavenum), sum(t.latenum), sum(t.abnormalnum), sum(t.illegaloutnum),sum(t.normalnum),cl.class, t.classcode, t.teachercode FROM t_attendance_day t,(SELECT * FROM t_class WHERE  schoolcode = :schoolcode";
		if(as.getGrade() != null && as.getGrade().length() > 0){
			sql += " and grade IN (:grade) ";
		}
		//加上学段 判断		
		if(as.getType() != null && as.getType().length() > 0){
					sql += "  AND TYPE = :type";
				}
				sql +=  ") cl  WHERE t.time < :endtime AND t.time > :starttime AND t.classcode=cl.classcode";
		if(as.getClasscode() != null && as.getClasscode().length() > 0 && grades.length == 1){
			sql += " and t.classcode in (:classcode)";
			as.setClasscode(as.getClasscode().replace(",", "^"));
		}
		if(as.getSchoolcode() != null && as.getSchoolcode().length() > 0){
			sql += " and cl.schoolcode = :schoolcode"; 
		}
		sql += " GROUP BY t.classcode,t.teachercode,t.studentnum ORDER BY cl.grade,cl.class ASC";
		if(as.getClasscode() != null && as.getClasscode().length() > 0 && grades.length == 1){
			as.setClasscode(as.getClasscode().replace(",", "^"));
		}
		if(grades.length > 1){
			//查询年级考勤
			sql = "SELECT info.grade,sum(info.studentnum) gsnum,SUM(info.leavenums) gslnum,SUM(info.latenums) gslanum,SUM(info.abnormalnums) gsanum,"
					+ "SUM(info.illegaloutnums) gsinum from ("+sql+")info group by info.grade order by info.grade asc";
		}
		if(as.getRolecode() != null && as.getRolecode().length() > 0){
			as.setRolecode(as.getRolecode().replace(",", "^"));
			sql = "select t1.* from("+sql+")t1,(select * from t_data_role where rolecode in (:rolecode))t2 where t1.classcode = t2.classcode";
		}
		
		as.setGrade(as.getGrade().replace(",", "^"));
		List<Object[]> list = null;
		if(as.getCurrentpage() != null && as.getCurrentpage() > 0 && as.getPagesize() != null && as.getPagesize() >0){
			list = getDao().queryBySqlPage(sql,as,as.getCurrentpage(),as.getPagesize());
		}else{
			list = getDao().queryBySQL(sql,as);
		}
		List<AttendanceStaticsQueryResult> result = new ArrayList<AttendanceStaticsQueryResult>();
		int studnum = 0;
		int leavnum = 0;
		int latenum = 0;
		int absenum = 0;
		int illanum = 0;
		int atttimes = 0;
		for (Object[] obj : list) {
			AttendanceStaticsQueryResult asqr = new AttendanceStaticsQueryResult();
			asqr.setGrade(String.valueOf(obj[0]));
			asqr.setName(asqr.getGrade()+"年级");
			if(grades.length == 1){
				asqr.setName(asqr.getName()+String.valueOf(obj[7])+"班");
				asqr.setClazz(String.valueOf(obj[7]));
				asqr.setClasscode(String.valueOf(obj[8]));
			}
			asqr.setStunums(Integer.parseInt(String.valueOf(obj[1])));
			asqr.setLeavenums(Integer.parseInt(String.valueOf(obj[2])));
			asqr.setLatenums(Integer.parseInt(String.valueOf(obj[3])));
			asqr.setAbsencenums(Integer.parseInt(String.valueOf(obj[4])));
			asqr.setIllegaloutnums(Integer.parseInt(String.valueOf(obj[5])));
			asqr.setNormalnums(Integer.parseInt(String.valueOf(obj[6])));
			int t = asqr.getLeavenums()+asqr.getLatenums()+asqr.getAbsencenums()+asqr.getNormalnums();
			int s = asqr.getStunums();
			double d =((t*1.00)/s);
			int times = (int) Math.ceil(d);
			asqr.setTimes(times*asqr.getStunums());
			//换算比例
			asqr.setLeavescale(getParsentString(asqr.getTimes(),asqr.getLeavenums()));
			asqr.setLatescale(getParsentString(asqr.getTimes(),asqr.getLatenums()));
			asqr.setAbsencescale(getParsentString(asqr.getTimes(),asqr.getAbsencenums()));
			asqr.setIllegaloutscale(getParsentString(asqr.getTimes(),asqr.getIllegaloutnums()));
			//合计
			studnum = studnum + asqr.getStunums();
			leavnum = leavnum + asqr.getLeavenums();
			latenum = latenum + asqr.getLatenums();
			absenum = absenum + asqr.getAbsencenums();
			illanum = illanum + asqr.getIllegaloutnums();
			atttimes = atttimes + asqr.getTimes();
			result.add(asqr);
			
		}
		AttendanceStaticsQueryResult asqr = new AttendanceStaticsQueryResult();
		asqr.setTimes(atttimes);
		asqr.setStunums(studnum);
		asqr.setLeavenums(leavnum);
		asqr.setLatenums(latenum);
		asqr.setAbsencenums(absenum);
		asqr.setIllegaloutnums(illanum);
		//换算比例
		asqr.setLeavescale(getParsentString(asqr.getTimes(),asqr.getLeavenums()));
		asqr.setLatescale(getParsentString(asqr.getTimes(),asqr.getLatenums()));
		asqr.setAbsencescale(getParsentString(asqr.getTimes(),asqr.getAbsencenums()));
		asqr.setIllegaloutscale(getParsentString(asqr.getTimes(),asqr.getIllegaloutnums()));
		asqr.setName("合计");
		result.add(asqr);
		Map map = new HashMap();
		map.put("total", result.size()-1);
		map.put("list", result);
		return map;
	}
	public String getParsentString(int x,int y){
		String result ="0";
		if(x > 0 && y > 0){
			DecimalFormat df = new DecimalFormat("0.00");  
			Double s = Double.valueOf(y) / Double.valueOf(x) * 100.00;
			result = df.format(s);
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map queryAttendanceByTeacher(AttendanceIntoSchoolInfo as) {
		String sql = "SELECT t.studentcode,s.name,SUM(t.type=1) leavetimes,SUM(t.type=2) latetimes,SUM(t.type=3) absencetimes,SUM(t.type=5) illegalouttimes ";
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			sql += " ,t.timetype";
		}
		sql += " FROM t_attendance t,(SELECT * FROM t_student WHERE classcode = :classcode)s WHERE t.classcode = :classcode AND "
			+ " t.time < :endtime AND t.time > :starttime and t.studentcode = s.studentcode ";
		if(as.getSchoolcode() != null && as.getSchoolcode().length() > 0){
			sql += " and s.schoolid = :schoolcode"; 
		}
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			as.setTimetype(as.getTimetype().replace(",", "^"));
			sql += " and t.timetype in (:timetype) GROUP BY t.studentcode,t.timetype ORDER BY t.timetype,CONVERT(s.name USING gbk) asc";
		}else{
			sql += " GROUP BY t.studentcode ORDER BY CONVERT(s.name USING gbk) asc";
		}
		
		String totalsql = "SELECT COUNT(info.studentcode) sutnums,IFNULL(SUM(info.leavetimes),0) ls,IFNULL(SUM(info.latetimes),0) las,IFNULL(SUM(info.absencetimes),0) abses,IFNULL(SUM(info.illegalouttimes),0) illes ";
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			as.setTimetype(as.getTimetype().replace(",", "^"));
			totalsql += " ,info.timetype FROM ("+sql+") info GROUP BY info.timetype";
		}else{
			totalsql += " FROM ("+sql+") info";
		}
		
		List<Object[]> list = null;
		List<Object[]> listtotal = getDao().queryBySQL(totalsql,as);
		if(as.getCurrentpage() != null && as.getCurrentpage() > 0 && as.getPagesize() != null && as.getPagesize() >0){
			list = getDao().queryBySqlPage(sql,as,as.getCurrentpage(),as.getPagesize());
		}else{
			list = getDao().queryBySQL(sql,as);
		}
		List<AttendanceStaticsQueryResultTeacher> result = new ArrayList<AttendanceStaticsQueryResultTeacher>();
		for (Object[] obj : list) {
			AttendanceStaticsQueryResultTeacher aqrt = new AttendanceStaticsQueryResultTeacher();
			aqrt.setStudentcode(String.valueOf(obj[0]));
			aqrt.setName(String.valueOf(obj[1]));
			aqrt.setLeavetimes(Long.valueOf((String.valueOf(obj[2]))));
			aqrt.setLatetimes(Long.valueOf((String.valueOf(obj[3]))));
			aqrt.setAbsencetimes(Long.valueOf((String.valueOf(obj[4]))));
			aqrt.setIllegalouttimes(Long.valueOf((String.valueOf(obj[5]))));
			if(as.getTimetype() != null && as.getTimetype().length() >0){
				aqrt.setTimetype(Integer.parseInt((String.valueOf(obj[6]))));
			}
			result.add(aqrt);
		}
		Map timesmap = new HashMap();
		List resList = new ArrayList();
		List<AttendanceStaticsQueryResultTeacher> sd = null;
		AttendanceStaticsQueryResultTeacher old = null;
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			for (int i = 0; i < result.size(); i++) {
				AttendanceStaticsQueryResultTeacher aqrt = result.get(i);
				if(old == null || old.getTimetype() != aqrt.getTimetype()){
					if(sd != null){
						timesmap = new HashMap();
						timesmap.put("timetype", old.getTimetype());
						timesmap.put("list",sd);
						resList.add(timesmap);
					}
					sd = new ArrayList<AttendanceStaticsQueryResultTeacher>();
				}else{
					sd.add(old);
				}
				old = aqrt;
			}
		}
		List<AttendanceStaticsQueryResultTeacher> resulttatol = new ArrayList<AttendanceStaticsQueryResultTeacher>();
		for (Object[] ss : listtotal) {
			AttendanceStaticsQueryResultTeacher aqrt = new AttendanceStaticsQueryResultTeacher();
			aqrt.setStudentNumber(String.valueOf(ss[0]));
			aqrt.setName("合计");
			aqrt.setLeavetimes(Long.valueOf((String.valueOf(ss[1]))));
			aqrt.setLatetimes(Long.valueOf((String.valueOf(ss[2]))));
			aqrt.setAbsencetimes(Long.valueOf((String.valueOf(ss[3]))));
			aqrt.setIllegalouttimes(Long.valueOf((String.valueOf(ss[4]))));
			if(as.getTimetype() != null && as.getTimetype().length() >0){
				aqrt.setTimetype(Integer.parseInt((String.valueOf(ss[5]))));
			}
			resulttatol.add(aqrt);
		}
		Map map = new HashMap();
		map.put("total", resulttatol);
		map.put("list", result);
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			timesmap = new HashMap();
			timesmap.put("timetype", old.getTimetype());
			timesmap.put("list",sd);
			resList.add(timesmap);
			map.put("list", resList);
		}
		return map;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map queryIntoSchoolRecord(IntoSchoolRecordQueryResult isrqr) {
		String sql = "SELECT t.imei,t.studentcode,t.classcode,t.grade,t.class clazz,t.name,t.studytype,t.usercode,CAST(t.status AS CHAR)"
				+ " status,CAST(t.time AS CHAR) time,t.remark FROM t_intoschool_record t,t_student ts WHERE 1=1 AND t.studentcode = ts.studentcode";
		if(isrqr.getStudentcode() != null && isrqr.getStudentcode().length() > 0){
			sql += " and t.studentcode = :studentcode";
		}
		if(isrqr.getClasscode() != null && isrqr.getClasscode().length() > 0){
			isrqr.setClasscode(isrqr.getClasscode().replace(",", "^"));
			sql += " and t.classcode in (:classcode)";
		}
		if(isrqr.getGrade() != null && isrqr.getGrade().length() > 0){
			isrqr.setGrade(isrqr.getGrade().replace(",", "^"));
			sql += " and t.grade in (:grade)";
		}
		if(isrqr.getClazz() != null && isrqr.getClazz().length() > 0){
			sql += " and t.class = :class";
		}
		if(isrqr.getImei() != null && isrqr.getImei().length() > 0){
			sql += " and t.imei = :imei";
		}
		if(isrqr.getStatus() != null && isrqr.getStatus().length() > 0){
			sql += " and t.status = :status";
		}
		if(isrqr.getStudytype() != null && isrqr.getStudytype().length() > 0){
			sql += " and t.studytype = :studytype";
		}
		if(isrqr.getName() != null && isrqr.getName().length() > 0){
			sql += " and t.name like :name";
			isrqr.setName("%"+isrqr.getName()+"%");
		}
		if(isrqr.getUsercode() != null && isrqr.getUsercode().length() > 0){
			sql += " and t.usercode = :usercode";
		}
		if(isrqr.getEndtime() != null && isrqr.getStarttime()!= null){
			sql += " and t.time < :endtime and t.time > :starttime";
		}
		if(isrqr.getSchoolcode() != null && isrqr.getSchoolcode().length() > 0){
			sql += " and ts.schoolid = :schoolcode";
		}
		if(isrqr.getRemark() != null ){
			sql += " and t.remark = :remark";
		}
		sql += " order by t.time desc";
		if(isrqr.getRolecode() != null && isrqr.getRolecode().length() > 0){
			isrqr.setRolecode(isrqr.getRolecode().replace(",", "^"));
			sql = "select t1.* from ("+sql+")t1,(select * from t_data_role where rolecode in (:rolecode))t2,(SELECT * FROM t_class ) t3 where t1.classcode = t2.classcode AND t2.classcode = t3.classcode";
			if(isrqr.getType() != null && isrqr.getType().length() > 0){
				sql += " and t3.type = :type";
			}
		}
		sql = "select * from ("+sql+")info";
		List<Object[]> listtotal = getDao().queryBySQL(sql,isrqr);
		List<IntoSchoolRecordQueryResult> list = null;
		if(isrqr.getCurrentpage() != null && isrqr.getCurrentpage() > 0 && isrqr.getPagesize() != null && isrqr.getPagesize() >0){
			Query query  = getDao().getSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(IntoSchoolRecordQueryResult.class));
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), isrqr,query);
			query.setFirstResult((int) ((isrqr.getCurrentpage()-1)*isrqr.getPagesize()));
			query.setMaxResults(isrqr.getPagesize().intValue());
			list = query.list();
		}else{
			Query query  = getDao().getSession().createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(IntoSchoolRecordQueryResult.class));
			query = ReflectionUtils.reflectQuery(query.getNamedParameters(), isrqr,query);
			list = query.list();
		}
		getDao().closeSession();
		Map map = new HashMap();
		map.put("total", listtotal.size());
		map.put("list", list);
		return map;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map queryAttendanceForUser(IntoSchoolRecordQueryResult isrqr) {
		String sql = "SELECT COUNT(t.studentcode) times,SUM(t.type=1) leavetimes,SUM(t.type=2) latetimes,SUM(t.type=3) "
				+ "absencetimes,SUM(t.type=5) illegalouttimes  FROM  t_attendance t WHERE t.studentcode = '"+isrqr.getStudentcode()+"'";
		String hql = "From TAttendance t where t.studentcode = :studentcode";
		if(isrqr.getStarttime() != null && isrqr.getStarttime() > 0){
			sql += " AND t.time > " + isrqr.getStarttime();
			hql += " AND t.time > :starttime";
		}
		if(isrqr.getEndtime() != null && isrqr.getEndtime() > 0){
			sql += " AND t.time < " + isrqr.getEndtime();
			hql += " AND t.time < :endtime" ;
		}
		List<Object[]> list = getDao().queryBySQL(sql);
		List<TAttendance> listatt = getDao().queryByHQL(hql,isrqr);
		List<AttendanceStaticsQueryResult> result = new ArrayList<AttendanceStaticsQueryResult>();
		Map map = new HashMap();
		if(listatt.size() > 0){
			for (Object[] obj : list) {
				AttendanceStaticsQueryResult asqr = new AttendanceStaticsQueryResult();
				asqr.setTimes(Integer.parseInt(String.valueOf(obj[0])));
				asqr.setLeavenums(Integer.parseInt(String.valueOf(obj[1])));
				asqr.setLatenums(Integer.parseInt(String.valueOf(obj[2])));
				asqr.setAbsencenums(Integer.parseInt(String.valueOf(obj[3])));
				asqr.setIllegaloutnums(Integer.parseInt(String.valueOf(obj[4])));
				result.add(asqr);
			}
			map.put("list", result);
			map.put("details", listatt);
		}
		return map;
	}
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map queryAttendanceForUserWeb(IntoSchoolRecordQueryResult isrqr) {
		String sql = "SELECT COUNT(t.studentcode) times,SUM(t.type=1) leavetimes,SUM(t.type=2) latetimes,SUM(t.type=3) "
				+ "absencetimes,SUM(t.type=5) illegalouttimes  FROM  t_attendance t WHERE t.studentcode = '"+isrqr.getStudentcode()+"'";
		String groupsql = "SELECT GROUP_CONCAT(t.type) typegroup,GROUP_CONCAT(DATE_FORMAT(FROM_UNIXTIME(t.time/1000),'%H:%i')) timegroup,GROUP_CONCAT(t.timetype) "
				+ "timetypegroup,DATE_FORMAT(FROM_UNIXTIME(t.time/1000),'%Y-%m-%d') tr FROM t_attendance t WHERE t.studentcode = '"+isrqr.getStudentcode()+"'";
		String hql = "From TAttendance t where t.studentcode = :studentcode";
		if(isrqr.getStarttime() != null && isrqr.getStarttime() > 0){
			sql += " AND t.time > " + isrqr.getStarttime();
			hql += " AND t.time > :starttime";
			groupsql += " AND t.time > " + isrqr.getStarttime();
		}
		if(isrqr.getEndtime() != null && isrqr.getEndtime() > 0){
			sql += " AND t.time < " + isrqr.getEndtime();
			hql += " AND t.time < :endtime";
			groupsql += " AND t.time > " + isrqr.getStarttime();
		}
		groupsql += " GROUP BY tr ORDER BY t.time asc";
		List<Object[]> list = getDao().queryBySQL(sql);
		List<Object[]> listgroup = getDao().queryBySQL(groupsql);
		List<TAttendance> listatt = getDao().queryByHQL(hql,isrqr);
		List<AttendanceWebUserQueryResult> result = new ArrayList<AttendanceWebUserQueryResult>();
		List<AttendanceStaticsQueryResult> resultta = new ArrayList<AttendanceStaticsQueryResult>();
		Map map = new HashMap();
		if(listatt.size() > 0){
			for (Object[] obj : list) {
				AttendanceStaticsQueryResult asqr = new AttendanceStaticsQueryResult();
				asqr.setTimes(Integer.parseInt(String.valueOf(obj[0])));
				asqr.setLeavenums(Integer.parseInt(String.valueOf(obj[1])));
				asqr.setLatenums(Integer.parseInt(String.valueOf(obj[2])));
				asqr.setAbsencenums(Integer.parseInt(String.valueOf(obj[3])));
				asqr.setIllegaloutnums(Integer.parseInt(String.valueOf(obj[4])));
				resultta.add(asqr);
			}
			for (Object[] obj : listgroup) {
				AttendanceWebUserQueryResult asqr = new AttendanceWebUserQueryResult();
				String[] types = String.valueOf(obj[0]).split(",");
				String[] times = String.valueOf(obj[1]).split(",");
				String[] timetypes = String.valueOf(obj[2]).split(",");
				for (int i = 0; i < timetypes.length; i++) {
					String timetype = timetypes[i];
					switch (Integer.parseInt(timetype)) {
					case 1:
						asqr.setMoin(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					case 2:
						asqr.setMoout(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					case 3:
						asqr.setNoin(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					case 4:
						asqr.setNoout(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					case 5:
						asqr.setNiin(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					case 6:
						asqr.setNiout(types[i].equalsIgnoreCase("3") ? "--" : times[i]);
						break;
					default:
						break;
					}
				}
				asqr.setDay(String.valueOf(obj[3]));
				result.add(asqr);
			}
			map.put("list", resultta);
			map.put("details", result);
			map.put("listatt", listatt);
		}
		return map;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map queryAttendanceByTeacherApp(
			AttendanceIntoSchoolInfo as) {
		String sqlstu = "SELECT ta.studentcode,ta.type,ta.timetype,ta.time,tt.name "
				+ "FROM (select * from t_attendance where classcode = :classcode and time < :endtime AND time > :starttime) ta,"
				+ "(select * from t_student where  classcode = :classcode) tt WHERE ta.studentcode = tt.studentcode ";
		String sql = "SELECT t.studentcode,s.name,SUM(t.type=1) leavetimes,SUM(t.type=2) latetimes,SUM(t.type=3) absencetimes,SUM(t.type=5) illegalouttimes ";
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			sql += " ,t.timetype";
			sqlstu += " and ta.timetype in (:timetype)";
		}
		sqlstu += " ORDER BY CONVERT(tt.name USING gbk) asc";
		sql += " FROM t_attendance t,(SELECT * FROM t_student WHERE classcode = :classcode)s WHERE t.classcode = :classcode AND "
			+ " t.time < :endtime AND t.time > :starttime and t.studentcode = s.studentcode ";
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			sql += " and t.timetype in (:timetype) GROUP BY t.studentcode,t.timetype ORDER BY t.timetype,CONVERT(s.name USING gbk) asc";
		}else{
			sql += " GROUP BY t.studentcode ORDER BY CONVERT(s.name USING gbk) asc";
		}
		
		String totalsql = "SELECT COUNT(info.studentcode) sutnums,SUM(info.leavetimes) ls,SUM(info.latetimes) las,SUM(info.absencetimes) abses,SUM(info.illegalouttimes) illes ";
		if(as.getTimetype() != null && as.getTimetype().length() >0){
			as.setTimetype(as.getTimetype().replace(",", "^"));
			totalsql += " ,info.timetype FROM ("+sql+") info GROUP BY info.timetype";
		}else{
			totalsql += " FROM ("+sql+") info";
		}
		List<Object[]> list = null;
		List<Object[]> listtotal = getDao().queryBySQL(totalsql,as);
		if(as.getCurrentpage() != null && as.getCurrentpage() > 0 && as.getPagesize() != null && as.getPagesize() >0){
			list = getDao().queryBySqlPage(sqlstu,as,as.getCurrentpage(),as.getPagesize());
		}else{
			list = getDao().queryBySQL(sqlstu,as);
		}
		List<AttendanceStudentInfo> mornings = new ArrayList<AttendanceStudentInfo>();
		List<AttendanceStudentInfo> noons = new ArrayList<AttendanceStudentInfo>();
		List<AttendanceStudentInfo> nights = new ArrayList<AttendanceStudentInfo>();
		for (Object[] obj : list) {
			AttendanceStudentInfo aqrt = new AttendanceStudentInfo();
			aqrt.setStudentcode(String.valueOf(obj[0]));
			aqrt.setName(String.valueOf(obj[4]));
			aqrt.setType(Long.valueOf((String.valueOf(obj[1]))));
			aqrt.setTime(Long.valueOf((String.valueOf(obj[3]))));
			aqrt.setTimetype(Integer.parseInt((String.valueOf(obj[2]))));
			if(aqrt.getTimetype() == 1){
				mornings.add(aqrt);
			}else if(aqrt.getTimetype() == 3){
				noons.add(aqrt);
			}else if(aqrt.getTimetype() == 5){
				nights.add(aqrt);
			}
		}
		List<AttendanceStaticsQueryResultTeacher> resulttatol = new ArrayList<AttendanceStaticsQueryResultTeacher>();
		for (Object[] ss : listtotal) {
			AttendanceStaticsQueryResultTeacher aqrt = new AttendanceStaticsQueryResultTeacher();
			aqrt.setStudentNumber(String.valueOf(ss[0]));
			aqrt.setName("合计");
			aqrt.setLeavetimes(Long.valueOf((String.valueOf(ss[1]))));
			aqrt.setLatetimes(Long.valueOf((String.valueOf(ss[2]))));
			aqrt.setAbsencetimes(Long.valueOf((String.valueOf(ss[3]))));
			aqrt.setIllegalouttimes(Long.valueOf((String.valueOf(ss[4]))));
			if(as.getTimetype() != null && as.getTimetype().length() >0){
				aqrt.setTimetype(Integer.parseInt((String.valueOf(ss[5]))));
			}
			resulttatol.add(aqrt);
		}
		Map map = new HashMap();
		map.put("total", resulttatol);
		map.put("mornings", mornings);
		map.put("noons", noons);
		map.put("nights", nights);
		return map;
	}
	/**
	 * 考勤补签
	 * @author:wuqiwei
	 * @param @param attendance
	 * @param @return
	 * @date:2016年7月29日上午10:00:07
	 */
	public boolean AttendanceRetroactiveByStudentcode(TAttendance attendance) {
		boolean flag = false;
		String sql = "update t_attendance set type = '6' where studentcode = '"+attendance.getStudentcode()+"' and time = '"+attendance.getTime()+"'";
		int a = getDao().executeSQL(sql);
		if( a > 0){
			flag = true;
		}
		return flag;
	}

	public Map queryIllegalleaveSchool(IntoSchoolRecordQueryResult isrqr) {
		Map map = new HashMap();
		String sql = " SELECT * FROM t_intoschool_record WHERE remark = '5' and flag = '"+isrqr.getFlag()+"'";
		if(isrqr.getEndtime() != null && isrqr.getStarttime()!= null){
			sql += " and time < "+isrqr.getEndtime()+" and time > "+isrqr.getStarttime()+"";
		}
		sql += " ORDER BY TIME desc ";
		if(isrqr.getRolecode() != null && isrqr.getRolecode().length() > 0){
			isrqr.setRolecode(isrqr.getRolecode().replace(",", "^"));
			sql = "select t1.* from ("+sql+")t1,(select * from t_data_role where rolecode in ('"+isrqr.getRolecode()+"'))t2,(SELECT * FROM t_class ) t3 where t1.classcode = t2.classcode AND t2.classcode = t3.classcode";
		}
		List<Object[]> list = getDao().queryBySqlPage(sql, isrqr, isrqr.getCurrentpage(), isrqr.getPagesize());
		List<IntoSchoolRecordQueryResult> result = new ArrayList<IntoSchoolRecordQueryResult>();
		for(int i = 0;i<list.size();i++){
			Object[] obj = list.get(i);
			IntoSchoolRecordQueryResult data = new IntoSchoolRecordQueryResult();
			data.setId(Long.valueOf(String.valueOf(obj[0])));
			data.setImei(String.valueOf(obj[1]));
			data.setStudentcode(String.valueOf(obj[2]));
			data.setClasscode(String.valueOf(obj[3]));
			data.setGrade(String.valueOf(obj[4]));
			data.setClazz(String.valueOf(obj[5]));
			data.setName(String.valueOf(obj[6]));
			data.setStudytype(String.valueOf(obj[7]));
			data.setUsercode(String.valueOf(obj[8]));
			data.setStatus(String.valueOf(obj[9]));
			data.setTime(String.valueOf(obj[10]));
			data.setFlag(String.valueOf(obj[11]));
			data.setRemark(String.valueOf(obj[12]));
			result.add(data);
		}
		map.put("data", result);
		map.put("count", list.size());
		return map;
	}

	/**修改非法离校信息
	 * @author:wuqiwei
	 * @param @param attendance
	 * @param @return
	 * @date:2016年9月26日下午4:24:37
	*/
	public boolean updateIllegalleaveSchool(TAttendance attendance) {
		boolean flag = false;
		String sql = " update t_intoschool_record set flag = '1' where id = '"+attendance.getId()+"'";
		int a = getDao().executeSQL(sql);
		if( a > 0){
			flag = true;
		}
		return flag;
	}
}
